# 人工智能NLP-Agent数字人项目-04-基金数据问答任务工单V1.1-20250214
import csv
import sqlite3
import pandas as pd
import utils.configFinRAG as configFinRAG

# 封装数据库查询函数
def query_db(sql, cursor):
    try:
        cursor.execute(sql)
        result_cols = cursor.fetchall()
        exc_result = str(result_cols)
        success_flag = 1
    except Exception as e:
        exc_result = f"error: {str(e)}"
        success_flag = 0
        print(f"执行 SQL 时出错: {sql}，错误信息: {e}")
    return success_flag, exc_result

if __name__ == '__main__':
    try:
        # 读取生成的 SQL 文件
        question_sql_file = pd.read_csv(configFinRAG.question_sql_path, delimiter=",", header=0)

        # 数据库连接
        db_path = r'C:\Users\86138\Desktop\项目结合\Fay-fay-agent-edition0830\agent\tools\data\博金杯比赛数据.db'
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()

            # 打开 SQL 执行结果文件
            with open(configFinRAG.question_sql_check_path, 'w', newline='', encoding='utf-8-sig') as file:
                csvwriter = csv.writer(file)
                csvwriter.writerow(['问题id', '问题', 'SQL', 'flag', '执行结果'])

                # 遍历问题 SQL 文件中的每一行
                for _, row in question_sql_file.iterrows():
                    sql = row['SQL']
                    if sql != 'error':
                        success_flag, exc_result = query_db(sql, cursor)
                        csvwriter.writerow([str(row['问题id']), str(row['问题']), sql, success_flag, exc_result])
                    else:
                        print(f"跳过 SQL 为 'error' 的问题: {row['问题']}")

    except Exception as e:
        print(f"程序出现错误: {e}")

